'   Database backup utility:
'   ========================
'   Copyright (C) 2007  Shabdar Ghata (Email : ghata2002@gmail.com)

'   This program is free software: you can redistribute it and/or modify
'   it under the terms of the GNU General Public License as published by
'   the Free Software Foundation, either version 3 of the License, or
'   (at your option) any later version.

'   This program is distributed in the hope that it will be useful,
'   but WITHOUT ANY WARRANTY; without even the implied warranty of
'   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
'   GNU General Public License for more details.

'   You should have received a copy of the GNU General Public License
'   along with this program.  If not, see <http://www.gnu.org/licenses/>.

'   This program comes with ABSOLUTELY NO WARRANTY.

Imports System.Data
Imports System.Data.OleDb

Public Class frmGenerateScripts
    Dim OledbConnectionString As String

    Private Sub frmBackupDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Load_Default_Values()
    End Sub

    Private Sub cmdExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGenerateScripts.Click
        Dim file As System.IO.StreamWriter
        file = My.Computer.FileSystem.OpenTextFileWriter("c:\SQLBackup.bat", False)
        If Not IsNothing(dgExportTables) Then
            Dim DT As DataTable = CType(dgExportTables.DataSource, DataTable)
            Dim i As Integer
            For i = 0 To DT.Rows.Count - 1
                Dim sBCPCommand As String = Generate_BCPCommand(txtServerName.Text, txtDatabaseName.Text, txtUsername.Text, txtPassword.Text, CStr(DT.Rows(i)("TABLENAME")), "", txtNoOfRows.Text)
                DT.Rows(i)("BCPCommand") = sBCPCommand
                file.WriteLine(sBCPCommand)
            Next
        End If
        file.Close()
        'MessageBox.Show("Export complete", "Export", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Shell("c:\sqlbackup.bat", AppWinStyle.NormalFocus)
    End Sub

    Sub Fill_Export_Table_Names()
        Dim tableNames As New DataTable
        Dim oScript As New clsScript
        'Dim fieldNames As New DataTable
        'OledbConnectionString = Get_Connection_String()
        'Dim CN As New OleDbConnection(OledbConnectionString)
        Try
            'CN.Open()
            'Dim n() As Object = {Nothing, Nothing, Nothing, "TABLE"}
            ''Dim n() As Object = {System.DBNull.Value, System.DBNull.Value, System.DBNull.Value, "TABLE"}
            ''tableNames = CN.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, n)
            'tableNames = oScript.GetTableNames(txtServerName.Text, txtDatabaseName.Text, txtUsername.Text, txtPassword.Text)
            'n(3) = Nothing
            'fieldNames = CN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, n)
            'CN.Close()
            Generate_Table_Names_Grid(tableNames)
            'dgExportTables.DisplayMember = "TABLE_NAME"
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Sub Load_Default_Values()
        txtServerName.Text = "SHABDAR\SQLEXPRESS"
        txtDatabaseName.Text = "SMS"
        txtUsername.Text = "safdar"
        txtPassword.Text = "ghata"
        rdSQLServerAuthentication.Checked = True
        txtBackupDir.Text = "C:\DBBackup\"
    End Sub
    Function Get_Connection_String()
        Return "Provider=SQLOLEDB.1;data source=" + txtServerName.Text + ";user id=" + txtUsername.Text + ";Password=" + txtPassword.Text + ";database=" + txtDatabaseName.Text + ""
    End Function
    Sub Generate_Table_Names_Grid(ByRef tableNames As DataTable)
        'Create datatable, which will be used to fill grid
        Dim i As Integer
        Dim DT As New DataTable
        DT.Columns.Add("ObjectName", "".GetType)
        DT.Columns.Add("Select", True.GetType)
        For i = 0 To tableNames.Rows.Count - 1
            Dim sBCPCommand As String = Generate_BCPCommand(txtServerName.Text, txtDatabaseName.Text, txtUsername.Text, txtPassword.Text, CStr(tableNames.Rows(i)("TABLE_NAME")), "", txtNoOfRows.Text)
            DT.Rows.Add(New Object() {tableNames.Rows(i)("TABLE_NAME"), True, txtNoOfRows.Text, "", sBCPCommand})
        Next
        dgExportTables.DataSource = Nothing
        dgExportTables.DataSource = DT
        'dgExportTables.AutoResizeColumns()
        dgExportTables.Columns("TableName").Width = 150
        dgExportTables.Columns("Export").Width = 50
        dgExportTables.Columns("TotalRows").Width = 120
        dgExportTables.Columns("Condition").Width = 300
        dgExportTables.Columns("BCPCommand").Width = 300
    End Sub

    Private Sub cmdConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdConnect.Click
        Fill_Export_Table_Names()
    End Sub

    Private Sub cmdSelectAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSelectAll.Click
        If Not IsNothing(dgExportTables.DataSource) Then
            Dim i As Integer
            Dim DT As DataTable
            DT = CType(dgExportTables.DataSource, DataTable)
            For i = 0 To DT.Rows.Count - 1
                DT.Rows(i)("Export") = True
            Next
        End If
    End Sub
    Private Sub cmdDeSelectAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDeselectAll.Click
        If Not IsNothing(dgExportTables.DataSource) Then
            Dim i As Integer
            Dim DT As DataTable
            DT = CType(dgExportTables.DataSource, DataTable)
            For i = 0 To DT.Rows.Count - 1
                DT.Rows(i)("Export") = False
            Next
        End If
    End Sub
    Function Generate_BCPCommand(ByVal sServerName As String, ByVal sDatabaseName As String, ByVal sUserName As String, ByVal sPassword As String, ByVal sTableName As String, ByVal sCondition As String, ByVal sTotalRows As String)
        Dim sCommand As String = " bcp ""select " + sTotalRows + " from " + sDatabaseName + ".dbo." + sTableName + """ queryout """ + txtBackupDir.Text + sTableName + ".dat"" -S " + sServerName + " -U " + sUserName + " -P " + sPassword + " " + txtBCPOptions.Text
        Return sCommand
    End Function
End Class